package com.wlcj.www.model.data;

import java.util.ArrayList;
import java.util.List;

import com.jfinal.plugin.activerecord.Model;

public class PlatformAttention extends Model<PlatformAttention> {

	/**
	 * 
	 */
	private static final long serialVersionUID = -785350589675281307L;
	
	public static final PlatformAttention dao = new PlatformAttention();
	
	public List<Integer> getUserAttentionPlatformIds(int userId,String time) {
		List<Integer> pids = new ArrayList<Integer>();
		//String sql = "select platform_id from wd_new_platform_attention where user_id = ?";
		String sql = "SELECT p.platform_id as platform_id,wd.volume FROM wd_new_platform_attention p left join wd_new_data wd on p.platform_id=wd.platform_id where user_id=? and wd.data_date=? order by wd.volume desc";
		List<PlatformAttention> list = find(sql, userId,time);
		for(PlatformAttention pa:list) {
			pids.add(pa.getInt("platform_id"));
		}
		return pids;
	}
	public List<PlatformAttention> getDataList(String type,String userId) {
		String sql = "select wdnp.attention_id attention_id,wnd.* from wd_new_platform_attention wdnp,"
				+ "wd_new_data wnd where  wdnp.platform_id = wnd.platform_id and wnd.type = ? "
				+ "and wdnp.user_id = ? order by volume desc";
		return find(sql, type,userId);
	}
	
	public List<PlatformAttention> getDataListByorder(String type, String orderby,String userId) {
		String sql = "select wdnp.attention_id attention_id,wnd.* from wd_new_platform_attention wdnp,"
				+ "wd_new_data wnd where  wdnp.platform_id = wnd.platform_id and wnd.type = ? "
				+ "and wdnp.user_id = ? order by "+orderby;
		return find(sql, type,userId);
	}
	public List<PlatformAttention> getDataList(String mintime,String maxtime,String orderby,String userId) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT w.platform_id as platform_id,");//'平台id'
		sb.append("wdnp.attention_id attention_id,");//关注Id
		sb.append("sum(w.volume) as volume,");//'成交量'
		sb.append("avg(w.compound_interest) as compound_interest,");//'平均利率'
		sb.append("avg(w.average_loan_time) as average_loan_time,");//'平均借款期限'
		sb.append("sum(w.investment_num) as investment_num,");//'投资人数'
		sb.append("sum(w.borrower_num) as borrower_num,");//'借款人数'
		sb.append("w.30_inflows as 30_inflows,");//'近30日净流入'
		sb.append("w.cumulative_amount as cumulative_amount,");//'累计待还金额'
		sb.append("w.60_still as 60_still,");//'未来60日待还'
		sb.append("sum(w.volume)/sum(w.investment_num) as av_investment_amount,");//'人均投资金额'
		sb.append("sum(w.volume)/sum(w.borrower_num) as av_borrow_amount,");//'人均借款金额'
		sb.append("sum(w.volume)*(avg(w.average_loan_time)) as time_weighted_volume,");//'时间加权成交量'
		sb.append("sum(w.borrow_num) as borrow_num,");//'借款标数'
		sb.append("w.10_collecting as 10_collecting,");//'前十大土豪代收金额占比'
		sb.append("w.10_borrowers as 10_borrowers,");//'前十大借款人待还金额占比'
		sb.append("avg(w.full_biaoyong_time) as full_biaoyong_time,");//'满标用时'
		sb.append("w.leverage as leverage,");//'资金杠杆'
		sb.append("w.registered_capital as registered_capital,");//'注册资金'
		sb.append("w.operating_time as operating_time ");//'运营时间'
		sb.append("from wd_new_data w ,wd_new_platform_attention wdnp where ");
		sb.append("w.platform_id = wdnp.platform_id and w.data_date>=? and w.data_date<=? and wdnp.user_id = ? GROUP BY w.platform_id order by ?");
		
		return find(sb.toString(),mintime,maxtime,userId,orderby);
	}
	
	public List<PlatformAttention> getUserPlatform(int id,String time){
		String sql = "SELECT p.platform_id as platform_id,w.platform_name as platform_name,wd.volume FROM wd_new_platform_attention p inner join wd_p_platform w on p.platform_id=w.platform_id left join wd_new_data wd on p.platform_id=wd.platform_id where user_id=? and wd.data_date=? order by wd.volume desc";
		return find(sql,id,time);
	}
}
